package db.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JDBCUtil {

    /**
     * mysql链接数据库
     */
    public static Connection getMySqlConnection(String path, String port, String serverName, String user, String password) {
        Connection conn = null;
        String url = "jdbc:mysql://" + path + ":" + port + "/" + serverName + "?user=" + user + "&password=" + password + "&characterEncoding=UTF-8";  //jdbc:mysql://localhost:3306/test?user=root&password=your_password;
        try {
            System.out.println("url:" + url);
            //1.加载驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            //2.获得数据库链接
            conn = DriverManager.getConnection(url);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * oracle链接数据库
     */
    public static Connection getOracleConnection(String path, String port, String serverName, String user, String password) {
        Connection conn = null;
        String url = "jdbc:oracle:thin:@" + path + ":" + port + ":" + serverName;  //jdbc:oracle:thin:@localhost:1521:orcl;
        try {
            //1.加载驱动程序
            Class.forName("oracle.jdbc.OracleDriver");
            //2.获得数据库链接
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }



    public static void release(ResultSet rs, Statement st, Connection conn) {
        closeRs(rs);
        closeSt(st);
        closeConn(conn);
    }

    private static void closeRs(ResultSet rs) {
        //释放资源
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        } finally {
            rs = null;
        }
    }

    private static void closeSt(Statement st) {
        //释放资源
        try {
            if (st != null) {
                st.close();
            }

        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        } finally {
            st = null;
        }
    }


    public void test2() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getOracleConnection("192.168.1.198", "1521",
                    "shwlpm", "sps_business", "sps_business");
//            conn.setAutoCommit(false);
//
//            String sql = "insert into oracle1 values (?,?,?,?,?,?)";
//            ps = conn.prepareStatement(sql);
//            long time = System.currentTimeMillis();
//            for (int i = 10000000; i < 120000000; i++) {
//                ps.setInt(1,i+1);
//                ps.setString(2,"name"+i);
//                ps.setInt(3,+i);
//                ps.setString(4,"lieOne"+i);
//                ps.setString(5,"lieTwo"+i);
//                ps.setTimestamp(6,new Timestamp(new Date().getTime()+i*60*1000));
//                ps.addBatch();
//            }
//            ps.executeBatch();
//            conn.commit();
//            long time1 = System.currentTimeMillis() - time;
//            System.out.println(time1);

            String sql = "PACKAGE_USER2.PERSON_QUERY2(?,'2019-05-23 19:33:44','2019-05-23 19:36:44')";
            CallableStatement cs = conn.prepareCall("{call "+sql+"}");
//            cs.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);

            cs.execute();
            // 获取返回结果
            ResultSet rs = (ResultSet) cs.getObject(1);
            //将resultSet对象转换为ArrayList对象
            List<Map<String,Object>> returnList = new ArrayList<Map<String,Object>>();
            ResultSetMetaData md;
            md = rs.getMetaData();
            int columnCount = md.getColumnCount(); //Map rowData;
            while (rs.next()) { //rowData = new HashMap(columnCount);
                Map rowData = new HashMap();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i), rs.getObject(i));
                }
                returnList.add(rowData);
            }

            System.out.println(returnList);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(null,ps,conn);
        }
    }
    private static void closeConn(Connection conn) {
        //释放资源
        try {
            if (conn != null) {
                conn.close();
            }

        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        } finally {
            conn = null;
        }
    }

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getOracleConnection("192.168.1.198", "1521",
                    "shwlpm", "sps_business", "sps_business");

            String sql = "CREATE GLOBAL TEMPORARY TABLE TMP_TEST\n" +
                    "(\n" +
                    "ID NUMBER ,\n" +
                    "NAME VARCHAR2(32)\n" +
                    ") ";

            Statement st = conn.createStatement();
            st.execute(sql);
            System.out.println("创建成功！");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            release(null,ps,conn);
        }
    }

}
